from collections.abc import Sequence
from sqlalchemy import RowMapping, text
from sqlalchemy.ext.asyncio import AsyncSession


class CodeCurd:

    """ 代码生成服务 """
    @staticmethod
    async def get_tables(*, db: AsyncSession, table_schema: str) -> Sequence[RowMapping]:
        """ 获取数据库表信息 """

        sql = """
            SELECT table_name AS table_name, table_comment AS table_comment 
              FROM information_schema.tables 
             WHERE table_name NOT LIKE 'sys_gen_%' 
               AND table_schema = :table_schema
            """
        result = await db.execute(text(sql), {"table_schema": table_schema})
        return result.mappings().all()


    @staticmethod
    async def get_table(*, db: AsyncSession, table_schema:str, table_name: str) -> RowMapping:
        """ 获取数据库表信息 """

        sql = """
            SELECT table_name AS table_name, table_comment AS table_comment 
              FROM information_schema.tables 
             WHERE table_name = :table_name 
               AND table_schema = :table_schema
            """
        result = await db.execute(text(sql), {"table_schema": table_schema, "table_name": table_name})  
        return result.mappings().first()

    @staticmethod
    async def get_all_columns(*, db: AsyncSession, table_schema: str, table_name: str) -> Sequence[RowMapping]:
        """ 获取数据库表所有列信息 """

        sql = """
            SELECT column_name AS column_name,
              CASE WHEN column_key = 'PRI' THEN 1 ELSE 0 END AS is_pk,
              CASE WHEN is_nullable = 'NO' OR column_key = 'PRI' THEN 0 ELSE 1 END AS is_nullable,
              ordinal_position AS sort,
              column_comment AS column_comment,
              character_maximum_length AS column_length,
              data_type AS column_type,
              column_default AS column_default
            FROM information_schema.columns
            WHERE table_schema = :table_schema
              AND table_name = :table_name
              AND column_name <> 'created_time'
              AND column_name <> 'updated_time'
            ORDER BY sort;
            """
        result = await db.execute(text(sql), {"table_schema": table_schema, "table_name": table_name})
        return result.mappings().all()
